package edu.cmu.vlis.wassup.db;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Properties;

import org.mybeans.dao.DAOException;
import org.mybeans.factory.BeanFactory;
import org.mybeans.factory.BeanFactoryException;
import org.mybeans.factory.BeanTable;
import org.mybeans.factory.MatchArg;
import org.mybeans.factory.RollbackException;
import org.mybeans.factory.Transaction;

import com.mysql.jdbc.Statement;

import edu.cmu.vlis.wassup.databean.UnprocessedEvent;
import edu.cmu.vlis.wassup.databean.UnprocessedEventTag;
import edu.cmu.vlis.wassup.databean.User;

/**
 * The class is used to access the database table Event
 * @author Jassica
 *
 */
public class UnprocessedEventDAO {
	// Private table used to store user's address information
	
	private BeanFactory<UnprocessedEvent> factory;
	
	private static void loadProperties(){
        Properties props = new Properties();
        try {
            props.load(new FileInputStream("src/properties"));
            System.getProperties().putAll(props);
            
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
	
	public UnprocessedEventDAO() throws DAOException {
		try {					
			BeanTable.useJDBC("com.mysql.jdbc.Driver", System.getProperty("org.mybeans.factory.jdbcURL")+
					"?user="+System.getProperty("org.mybeans.factory.user")
					+"&password="+System.getProperty("org.mybeans.factory.password"));
			// initialize table and bean factory
			UserDAO ud = new UserDAO();
			BeanTable<UnprocessedEvent> eventTable = BeanTable.getInstance(UnprocessedEvent.class,"unp_events", ud.getFactory()); // Get announcement table
	        
	        if (!eventTable.exists()) {
	        	eventTable.create("hashId");
	            
	            // Change table field for description to 1024 var char
	    		String query = "alter table unp_events modify description nvarchar(1024) default NULL";
	    		Connection con = null;

	    		
	    		try {

	    			Class.forName("com.mysql.jdbc.Driver").newInstance();
	    		    con = DriverManager.getConnection(System.getProperty("org.mybeans.factory.jdbcURL"),
	    		    		System.getProperty("org.mybeans.factory.user"), 
	    		    		System.getProperty("org.mybeans.factory.password"));

		            Statement sta = (Statement) con.createStatement(); 
		            int count = sta.executeUpdate(query);
		            sta.close();
		          
	    			con.close();		
	    			
	    		} catch (Exception e) {
	    			throw new DAOException(e);
	    		}
	        }
	        
	        // Long running web apps need to clean up idle database connections.
	        // So we can tell each BeanTable to clean them up.  (You would only notice
	        // a problem after leaving your web app running for several hours.)
	        eventTable.setIdleConnectionCleanup(true);
	
	        // Get a BeanFactory which the actions will use to read and write rows of the "user" table
	        factory = eventTable.getFactory();
		} catch (BeanFactoryException e) {
			throw new DAOException(e);
		}		
			
	}
	
	
	/**
	 * To check whether the given announcement_id exists
	 * @param announcement_id
	 * @return
	 * @throws DAOkException 
	 */
	public boolean isEventExist(String eventId) {
		boolean result = false;
		UnprocessedEvent event;
		
		try {
			event = factory.lookup(eventId);
			if (event != null) 
				result = true;
		} catch (RollbackException e) {
			e.printStackTrace();
		}
		
		return result;		
	}

	/** 
	 * Insert a new tuple of announcement into the beanTable
	 * Returns error message if creation fails.
	 * Otherwise return a null upon success.
	 * 
	 * @param event
	 * @param tags
	 * @return
	 * @throws RollbackException
	 */
	public String insert(UnprocessedEvent event, String tag, String URL) throws RollbackException 
	{
		System.out.println("insert called with " + event + " tag - " + tag);
		String message = createMessage(event);
		
		String hashId = hash(message);
		event.setHashId(hashId);
		
		UnprocessedEvent dbEvent = factory.lookup(hashId);
		
		if (dbEvent == null) {
			if (event.getEndDate() == null || event.getEndDate().trim().equals("")) {
				if (event.getStartDate() != null)
					event.setEndDate(event.getStartDate());
			}
			
			if (event.getEndTime() == null || event.getEndTime().trim().equals("")) {
				if (event.getStartTime() != null)
					event.setEndTime(event.getStartTime());
			}
			
			if (event.getDescription() == null || event.getDescription().trim().equals(""))
				event.setDescription(event.getName());
			
			insert(event, URL);
			//System.out.println("Event inserted in database " + event);
		}
		
		try {
			UnprocessedEventTagDAO eventTagDAO = new UnprocessedEventTagDAO();
			UnprocessedEventTag eventTag = eventTagDAO.lookup(event, tag);
			if (eventTag==null) {
				eventTagDAO.insert(event, tag);
			}
			// if both event and eventTag table has the inforamtion
			// the data is duplicated
			else { // if exception happens on insertion on the tag table
				//delete(ann.getAnnouncement_id());	// delete the record in the announcement table
				throw new RollbackException("Insertion fails: Duplication");
			}	
		}
		catch (DAOException e) {
			throw new RollbackException(e);
		}
		
		System.out.println("Success");
		return event.getHashId();
		
	}

	private void insert(UnprocessedEvent event, String URL) throws RollbackException {
		DBConnector dbConnector = new DBConnector("wassup", System.getProperty("org.mybeans.factory.jdbcLocation"));
		
		String query = "insert into unp_events values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		Connection con = dbConnector.getConnection();
		
		PreparedStatement insert = null;
		
		try {
//			Class.forName("com.mysql.jdbc.Driver").newInstance();
//		    con = DriverManager.getConnection(System.getProperty("org.mybeans.factory.jdbcURL"),
//		    		System.getProperty("org.mybeans.factory.user"), 
//		    		System.getProperty("org.mybeans.factory.password"));
		    
			insert = con.prepareStatement(query);
			insert.setString(1, event.getHashId());
			insert.setInt(2, event.getAccepts());
			insert.setString(3, event.getCity());
			insert.setString(4, event.getDescription());
			insert.setInt(5, event.getDontknow());
			insert.setString(6, event.getEndDate());			
			insert.setString(7, event.getEndTime());
			insert.setFloat(8, event.getLatitude());
			insert.setFloat(9, event.getLongitude());
			insert.setString(10, event.getName());
			insert.setInt(11, event.getRejects());
			insert.setString(12, event.getSender().getUserName());
			insert.setString(13, event.getStartDate());
			insert.setString(14, event.getStartTime());
			insert.setString(15, event.getState());
			insert.setString(16, event.getStreet());
			insert.setString(17, event.getUrl());
			
			
			insert.executeUpdate();
			
			insert.close();
			con.close();		
			
		} catch (Exception e) {
			throw new RollbackException(e);
		}
		
	}
	/**
	 * @param event
	 * @return
	 */
	private String createMessage(UnprocessedEvent event) {
		String message = "";
		message += "Subject: "+event.getName();
		message += "; ";
		message += "Start Date: "+event.getStartDate();
		message += "; ";
		message += "Start Time: "+event.getStartTime();
		message += "; ";
		
		message += "End Date: ";
		if (event.getEndDate()==null || event.getEndDate().trim().equals("")) {
			message += event.getStartDate();
		}
		else message += event.getEndDate();
		message += "; ";
		
		message += "End Time: ";
		if (event.getEndTime()==null || event.getEndTime().trim().equals("")) {
			message += event.getStartTime();
		}
		else message += event.getEndTime();
		message += "; ";
		
		message += "Location: ";
		message += event.getStreet() + " " + event.getCity() + " "+event.getState()+" "+event.getUrl();
		message += "; ";
		
		message += "Description: ";
		if (event.getDescription()==null || event.getDescription().trim().equals(""))
			message += event.getName();
		else message += event.getDescription();
		message += "; ";
		
		if (event.getSender()!=null)
			message += "Sender: "+event.getSender().getUserName();
		
		message = message.trim();
		return message;
	}
	
	/** Lookup whether a typical announcement exists in the table
	 * Return Not Found if the announcements information is missing
	 * Otherwise return detailed information about announcement of a user
	 * 
	 * @param announcement_id
	 * @return
	 * @throws RollbackException
	 */
	public UnprocessedEvent lookup(String eventId) throws DAOException {
		UnprocessedEvent event = null;
		try {
			event = factory.lookup(eventId);
			System.out.println(event);
		} catch (RollbackException e) {
			//e.printStackTrace();
			throw new DAOException(e);
		}		
		return event;
	}
	
		
	/**
	 * Get the hashvalue of the input message
	 * Stroe the hashvalue into the database to achieve primary key of the announcement
	 * @param message the user input clear-text message
	 * @return hash value of the input
	 */
	private String hash(String message) {
		MessageDigest md = null;
		try {
		  md = MessageDigest.getInstance("SHA1");
		} catch (NoSuchAlgorithmException e) {
		  throw new AssertionError("Can't find the SHA1 algorithm in the java.security package");
		}

		String saltString = String.valueOf(12345);
		
		md.update(saltString.getBytes());
		md.update(message.getBytes());
		byte[] digestBytes = md.digest();

		// Format the digest as a String
		StringBuffer digestSB = new StringBuffer();
		for (int i=0; i<digestBytes.length; i++) {
		  int lowNibble = digestBytes[i] & 0x0f;
		  int highNibble = (digestBytes[i]>>4) & 0x0f;
		  digestSB.append(Integer.toHexString(highNibble));
		  digestSB.append(Integer.toHexString(lowNibble));
		}
		String digestStr = digestSB.toString().trim();

		return digestStr;
	}
	
	/** Delete a tuple with primary key as announcement_id from announcement table
	 * return null if no tuple is affected
	 * 
	 * @param announcement_id
	 * @return
	 * @throws RollbackException
	 */
	public void delete(String eventId) throws DAOException {
		UnprocessedEvent event = null;
		try {
			event = factory.lookup(eventId);
		} catch (RollbackException e) {
			// TODO Auto-generated catch block
			//e.printStackTrace();
			throw new DAOException(e);
		}
		
		if (event != null) {
			// first delete event tags record for this event
			UnprocessedEventTagDAO eventTagDAO = new UnprocessedEventTagDAO();
			UnprocessedEventTag[] eventTags = eventTagDAO.getEventTagsForEvent(event);
			if (eventTags != null) {
				for (UnprocessedEventTag eventTag : eventTags) {
					eventTagDAO.delete(eventTag.getEventId(), eventTag.getTag());
				}
			}
			
//			// now delete events for user record
//			EventsForUserDAO eventsForUserDAO = new EventsForUserDAO();
//			EventsForUser[] eventsForUsers = eventsForUserDAO.getEventsForUser(event);
//			if (eventsForUsers != null) {
//				for (EventsForUser eventsForUser : eventsForUsers) {
//					eventsForUserDAO.delete(eventsForUser.getEventId(), eventsForUser.getUserId());
//				}
//			}
			
			try {
				if (!Transaction.isActive())				
					Transaction.begin();
				
				factory.delete(eventId);
				
				Transaction.commit();
			} catch (RollbackException e) {					
				e.printStackTrace();
			}			
		}
	}
		
	/**
	 * Get all the entries in one table
	 * @return
	 * @throws RollbackException
	 */
	public UnprocessedEvent[] getAll() {
		UnprocessedEvent[] allEvents = null;
		try {
			allEvents = factory.match();
		} catch (RollbackException e) {
			e.printStackTrace();
		}
		return allEvents;
	}
	
	/**
	 * Get all the announcements according to one user
	 * @param args
	 * @throws RollbackException 
	 * @throws RollbackException
	 */
	public UnprocessedEvent[] getFromSender(User me){
		UnprocessedEvent[] allEventsFromMe = null;
		try {
			allEventsFromMe = factory.match(MatchArg.equals("sender", me));
		} catch (RollbackException e) {
			e.printStackTrace();
		}
		return allEventsFromMe;
		
	
	}
	
	public BeanFactory<UnprocessedEvent> getFactory() {
		return factory;
	}
	
	public static void main(String[] args) throws RollbackException {		
		loadProperties();
		
		UnprocessedEventDAO eventDAO = null;
		UserDAO userDAO = null;
		
		String eventName = "GO INI Picnic";
		
		// create a new Event
		UnprocessedEvent picnic = new UnprocessedEvent("1");
		picnic.setName(eventName);
		picnic.setDescription("GO INI Spring Picnic - Bask in Sun");
		picnic.setStartDate("04-24-2009");
		picnic.setStartTime("12");
		picnic.setEndDate("04-24-2009");
		picnic.setCity("Pittsburgh");
		
		
		try {
			eventDAO = new UnprocessedEventDAO();
			userDAO = new UserDAO();
			
			User sender = userDAO.lookup("upcoming");
			picnic.setSender(sender);			
	
			String hashId1 = eventDAO.insert(picnic, "music", "CMU-291950.WV.CC.CMU.EDU");
			System.out.println("Picnic inserted HashId 1 " + hashId1);
			
			// TODO - this is a problem
//			String hashId2 = eventDAO.insert(picnic, "other");
			
//			eventDAO.insert(picnic);
			
//			System.out.println("hashId 2 " + hashId2);
			
//			System.out.println(" --- Lookup ----");
//			Event myEvent = eventDAO.lookup(hashId1);
//			System.out.println("myEvent = " + myEvent);
//			
//			Event[] allEvents = eventDAO.getAll();
//			System.out.println("All events - ");
//			for (int i = 0; i < allEvents.length; i++) {
//				Event event = allEvents[i];
//				System.out.println(event);
//			}			
			
		} catch (DAOException e1) {
			e1.printStackTrace();
		}
		
		System.exit(0);		
			
//		ArrayList<String> tags = new ArrayList<String>();
//		tags.add("music");
//		tags.add("movie");		
//		tags.add("TV");
//		String id = eventDAO.insert(event, "movie");	
//		try {
//			event = eventDAO.lookup(id);
//		} catch (DAOException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//		event.setCity("new york");
//		event.setDescription("Happy hour in NY");
////		user = new User("a");
////		event.setSender(user);
//		//eventDAO.update(1, event, tags);
//		eventDAO.lookup(id);
//		//eventDAO.delete(1);
//		
//		Event[] anns = eventDAO.getAll();
//		System.out.println(anns.length+" elements totally");
////		Announcement_Tag_Dao ud = new Announcement_Tag_Dao();
////		String tagss[] = ud.getTagFromAnnouncement(anns[0]);
////		System.out.println("User has "+tagss.length+" interests");
//		
//		
//		//eventDAO.createEditor();
//		/*eventDAO.insert( new Date(), "Celeberating", "All students");
//		Event announcement = eventDAO.lookup(1);
//		eventDAO.upinsert(1, announcement.getDate_time(), "Celeberating VLIS", "All students");
//		eventDAO.delete(2);*/
//		
//		//eventDAO.getTable();
	}
}
